package com.elite.groovy.callback

import com.elite.groovy.kh.KHUtil
import com.elite.groovy.util.EliteBean
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.support.rowset.SqlRowSet

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * @Title:同步组别人员
 * @Description:同步企微 、365数据库组别人员
 * @Param:
 * @return:
 * @Author: TH* @Date: 2023/10/27
 */
class autoSyncStaffGroup implements ServiceBackInvoke {

    private static final Log log = LogFactory.getLog(autoSyncStaffGroup.class);

    @Override
    String doCallBack(EliteBean eliteBean, HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {

        // 获取数据源List<Map<String, Object>>
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        JdbcTemplate jtQW = new JdbcTemplate(eliteBean.getDataSource("dataSourceQW"));
        JdbcTemplate jt365 = new JdbcTemplate(eliteBean.getDataSource("dataSource365"));

        try {
            long starttime = System.currentTimeMillis();
            //获取数据--先同步组别
            SqlRowSet rs = jtQW.queryForRowSet("select d.id,d.name,d.parentid,d.created_at,d.updated_at From depart d ");
            //遍历数据
            while (rs.next()) {
                String qw_id = rs.getString("id");//企微组别id
                String qw_name = rs.getString("name");//企微组别名字
                String qw_parentid = rs.getString("parentid");//企微父级ID
                String qw_created_at = rs.getString("created_at");//企微组别创建时间
                String qw_updated_at = rs.getString("updated_at");//企微组别修改时间
                log.debug("【企微组别id】qid：" + qw_id + "----【企微组别名字】name：" + qw_name + "------【企微父级ID】parentid：" + qw_parentid + "-----【企微组别创建时间】created_at：" + qw_created_at + "-----【企微组别修改时间】updated_at：" + qw_updated_at);


                String rolegroup_id = null;//定义组别变量
                String parent_id = null;//定义父级变量


                //查询当前取到的企微组id是否在系统存在（组id）
                String rsql = ''' select qw_ROLEGROUP_ID,ROLEGROUP_ID from ROLEGROUP where qw_ROLEGROUP_ID=?  ''';
                List<Map<String, Object>> selelctList = jt.queryForList(rsql, qw_id);

                if (selelctList.size() > 0) {
                    //发现已经存在->修改
                    rolegroup_id = selelctList.get(0).get("ROLEGROUP_ID");

                }

                //查询当前取到的父级组是否在系统中存在,不存在则新建一个(父级id)
                String rsql2 = '''select ROLEGROUP_ID from ROLEGROUP where qw_ROLEGROUP_ID=?  ''';
                List<Map<String, Object>> selelctList2 = jt.queryForList(rsql2, qw_parentid);
                if (selelctList2.size() > 0) {
                    parent_id = selelctList2.get(0).get("ROLEGROUP_ID");
                } else {
                    //父级name不等于空置
                    if (qw_parentid != "0") {
                        //开始新增父级组
                        parent_id = KHUtil.getRandomString(6); //生成六位随机id
                        def insertSql5 = "INSERT INTO ROLEGROUP (ROLEGROUP_ID,qw_ROLEGROUP_ID,STATUS,CREATEDDATE,CREATEDBY,source) VALUES('" + parent_id + "','" + qw_parentid + "',1,now(),'000023','1')";
                        eliteBean.update(insertSql5, 'dataSource1');
                    }
                }


                if (selelctList.size() > 0) {
                    //存在修改
                    log.debug("Elite系统存在该组别ID执行修改操作");
                    String upsql = '''UPDATE ROLEGROUP SET  ROLEGROUPDESCRIPTION=?,PARENT_ROLEGROUPID=? ,MODIFIEDDATE=now(),MODIFIEDBY='00023',source='1' WHERE ROLEGROUP_ID=?
                                    ''';
                    jt.update(upsql, qw_name, parent_id, rolegroup_id);
                    //先删后增
                    def updateSql3 = "delete from authority_relationship where lower='" + rolegroup_id + "' and HIGHERFLAG='0' and LOWERFLAG='0'";
                    def row = eliteBean.update(updateSql3, 'dataSource2');
                    log.debug("删除authority_relationship层级:" + rolegroup_id + "|" + row);
                    String insertSql4 = '''
                                INSERT INTO authority_relationship ( AUTHORITY_ID, HIGHER, HIGHERFLAG, LOWER, LOWERFLAG ) VALUES( uuid(), ?, 0,?, 0 ) 
                                ''';
                    jt.update(insertSql4, parent_id, rolegroup_id);

                } else {
                    //在系统表里找不到 组别--->开始新增组别
                    def ram_id = KHUtil.getRandomString(6).toUpperCase();//随机id
                    def insertSql1 = "INSERT INTO ROLEGROUP (ROLEGROUP_ID,qw_ROLEGROUP_ID,ROLEGROUPDESCRIPTION,STATUS,PARENT_ROLEGROUPID,CREATEDDATE,CREATEDBY,source) VALUES('" + ram_id + "','" + qw_id + "','" + qw_name + "','1','" + parent_id + "',now(),'000023','1')";
                    def dataresult = eliteBean.update(insertSql1, 'dataSource');
                    log.debug("执行ROLEGROUP数据新增SQL结果:" + dataresult.toString());


                    //插入表mprojectrolegroup话务员组和项目对应表
                    def insertsql3 = "INSERT INTO mprojectrolegroup(ROLEGROUP_ID, MPROJECT_ID, MPROJECTROLEGROUP_ID) VALUES ('" + ram_id + "', 'MMYSQL', uuid())";
                    def dataresult3 = eliteBean.update(insertsql3, 'dataSource');
                    log.debug("执行mprojectrolegroup数据新增SQL结果:" + dataresult3.toString());


                    if (!KHUtil.isNull(parent_id)) {
                        //获取父级关系--入层级表
                        def insertSql2 = "INSERT INTO authority_relationship ( AUTHORITY_ID, HIGHER, HIGHERFLAG, LOWER, LOWERFLAG ) VALUES( uuid(), '" + parent_id + "', 0, '" + ram_id + "', 0 )";
                        def dataresult2 = eliteBean.update(insertSql2, 'dataSource');
                        log.debug("执行authority_relationship数据新增SQL结果:" + dataresult2.toString());

                    }


                }

            }


            //******************************************************************开始同步企微人员信息************************************************************************************************


            //获取企微人员数据
            SqlRowSet rs2 = jtQW.queryForRowSet("select userid,name,mobile,department,position,gender,email,biz_mail,is_leader_in_dept,id,status,main_department,created_at,updated_at from qwlist");

            //循环遍历
            while (rs2.next()) {
                String userid = rs2.getString("userid");//成员USERID
                String name = rs2.getString("name");//成员名称
                String mobile = rs2.getString("mobile");//手机号码
                String department = rs2.getString("department");//成员所属部门
                String position = rs2.getString("position");//职务信息
                String gender = rs2.getString("gender");//性别
                String email = rs2.getString("email");//邮箱
                String biz_mail = rs2.getString("biz_mail");//企业邮箱
                String is_leader_in_dept = rs2.getString("is_leader_in_dept");//表示在所在的部门内是否为部门负责人。0-否 1 是
                String id = rs2.getString("id");//员工编号
                String status = rs2.getString("status");//激活状态 1 已激活  2 已禁用  4 为激活   5退出企业
                String main_department = rs2.getString("main_department");//主部门
                String created_at = rs2.getString("created_at");//企微人员创建时间
                String updated_at = rs2.getString("updated_at");//企微人员修改时间


                log.debug("[准备处理同步的人员]：" + userid + "---[姓名]" + name);
                String workstatus = null;
                String ISSUPERVISOR = null;
                String rolegroup_id = null;
                String staff_id = null;
                def ram_id = KHUtil.getRandomString(6).toUpperCase();//随机id


                //翻译------如果激活状态是5  那么就标记成离职
                if (status.equals("5")) {
                    workstatus = "QUIT";
                    name = name + "-停用";
                } else {
                    workstatus = "ONLINE";

                }
                //翻译------如果是部门负责人将设置成5
                if (is_leader_in_dept.equals("1")) {

                    ISSUPERVISOR = 5;

                } else {

                    ISSUPERVISOR = 1;

                }

                //判断人员是否已经存在
                String selectSql = '''SELECT STAFF_ID FROM STAFF WHERE userid=?  limit 1 ''';
                List<Map<String, Object>> selelctList = jt.queryForList(selectSql, userid);
                if (selelctList.size() > 0) {
                    staff_id = selelctList.get(0).get("STAFF_ID");
                }


                //判断部门是否存在
                def selectSql3 = "select ROLEGROUP_ID from ROLEGROUP where qw_ROLEGROUP_ID='" + department + "' limit 1";
                List sqlResult3 = eliteBean.chaxun_sql(selectSql3, 'dataSource');
                log.debug(sqlResult3.toString());
                if (sqlResult3.size() > 0) {
                    rolegroup_id = sqlResult3.get(0).get("ROLEGROUP_ID");
                } else {
                    log.debug("系统找不到该组：" + rolegroup_id);

                }


                if (selelctList.size() > 0) {
                    log.debug("已经存在staff表：" + staff_id);
                    //判断已经存在 ----执行修改
                    String updatesql = '''
                                    update staff set 
                                 staffname=?,
                                 WORKSTATUS=?,
                                 ISSUPERVISOR=?,
                                 loginname=?,
                                 defaultrolegroup=?,
                                 e_mail=?,
                                 GENDER=?,
                                 MOBILE=?,
                                 biz_mail=?,
                                 userid=?,
                                 department=?,
                                 position=?,
                                 is_leader_in_dept=?,
                                 status=?,
                                 qw_id=?,
                                 main_department=?,
                                 qw_sync_time=now(),
                                 MODIFIEDBY='000023',
                                 MODIFIEDDATE=now(),
                                 source='1'                    
                                 where staff_id=?
                                 ''';

                    jt.update(updatesql, name, workstatus, ISSUPERVISOR, userid, rolegroup_id, email, gender, mobile, biz_mail, userid, department, position, is_leader_in_dept, status, id, main_department, staff_id);

                    log.debug("查询到已有" + staff_id + "，执行更新staffrolegroup数据....");
                    def updateSql3 = "delete from staffrolegroup where staff_id='" + staff_id + "'";
                    def dataresult4 = eliteBean.update(updateSql3, 'dataSource');
                    log.debug("删除staffrolegroup:" + staff_id + "|" + dataresult4);

                    def updateSql2 = "INSERT INTO staffrolegroup(STAFF_ID, ROLEGROUP_ID) VALUES ('" + staff_id + "', '" + rolegroup_id + "')";
                    def dataresult5 = eliteBean.update(updateSql2, 'dataSource');
                    log.debug("更新staffrolegroup数据结果:" + staff_id + "|" + dataresult5);

                } else {
                    //查询不到执行新增staff

                    String insertsql = '''
                                 insert 
                                 into 
                                 staff 
                                 (
                                 staff_id,
                                 staffname,
                                 WORKSTATUS,
                                 ISSUPERVISOR,
                                 loginname,
                                 defaultrolegroup,
                                 e_mail,
                                 GENDER,
                                 MOBILE,
                                 biz_mail,
                                 userid,
                                 department,
                                 position,
                                 is_leader_in_dept,
                                 status,
                                 qw_id,
                                 main_department,
                                 qw_sync_time,
                                 CREATEDBY,
                                 CREATEDDATE,
                                 source
                                 )
                                 values
                                 (
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?, 
                                 ?,
                                 now(),
                                '000023',
                                 now(),
                                 '1'
                                 )
                                  ''';
                    jt.update(insertsql, ram_id, name, workstatus, ISSUPERVISOR, userid, rolegroup_id, email, gender, mobile, biz_mail, userid, department, position, is_leader_in_dept, status, id, main_department);

                    String insertSql2 = "INSERT INTO staffrolegroup(STAFF_ID, ROLEGROUP_ID) VALUES ('" + ram_id + "', '" + rolegroup_id + "')";
                    def dataresult2 = eliteBean.update(insertSql2, 'dataSource');
                    log.debug("执行staffrolegroup数据新增SQL结果:" + ram_id + "|" + dataresult2);


                }
            }

            long endtime = System.currentTimeMillis();

            log.debug("【企微同步人员组别信息】执行耗时:" + (endtime - starttime));

        } catch (Exception e) {
            log.error("【企微同步人员组别信息异常】：" + e);

        }


        try {
            long starttime = System.currentTimeMillis();


            log.debug("*********************************开始同步365数据库*********************************************");

            String sql = '''
                           SELECT
                           r1.id,
                         r1.org_name,
                        r1.waiqin365_parent_id,
                        r1.create_time,
                        r1.modify_time 
                             FROM
                        hds_organization_info r1 
                         WHERE
                        r1.full_codes LIKE '/davco%' 
                        and r1.org_status='1'
                    ''';


            //获取数据--先同步组别
            SqlRowSet rs = jt365.queryForRowSet(sql);
            //遍历数据
            while (rs.next()) {
                String qw_id = rs.getString("id");//企微组别id
                String qw_name = rs.getString("org_name");//企微组别名字
                String qw_parentid = rs.getString("waiqin365_parent_id");//企微父级ID
                String qw_created_at = rs.getString("create_time");//企微组别创建时间
                String qw_updated_at = rs.getString("modify_time");//企微组别修改时间
                log.debug("【365组别id】qid：" + qw_id + "----【365组别名字】name：" + qw_name + "------【365父级ID】parentid：" + qw_parentid + "-----【365组别创建时间】created_at：" + qw_created_at + "-----【365组别修改时间】updated_at：" + qw_updated_at);


                String rolegroup_id = null;//定义组别变量
                String parent_id = null;//定义父级变量


                //查询当前取到的企微组id是否在系统存在（组id）
                String rsql = ''' select rolegroup_365_id,ROLEGROUP_ID from ROLEGROUP where rolegroup_365_id=?  ''';
                List<Map<String, Object>> selelctList = jt.queryForList(rsql, qw_id);

                if (selelctList.size() > 0) {
                    //发现已经存在->修改
                    rolegroup_id = selelctList.get(0).get("ROLEGROUP_ID");

                }

                if (KHUtil.isNull(qw_parentid)) {

                    qw_parentid = 0;
                }

                //查询当前取到的父级组是否在系统中存在,不存在则新建一个(父级id)
                String rsql2 = '''select ROLEGROUP_ID from ROLEGROUP where rolegroup_365_id=?  ''';
                List<Map<String, Object>> selelctList2 = jt.queryForList(rsql2, qw_parentid);
                if (selelctList2.size() > 0) {
                    parent_id = selelctList2.get(0).get("ROLEGROUP_ID");
                } else {
                    //父级ID是0的话就不生产父级啦
                    if (qw_parentid != "0") {
                        //开始新增父级组
                        parent_id = KHUtil.getRandomString(6); //生成六位随机id
                        def insertSql5 = "INSERT INTO ROLEGROUP (ROLEGROUP_ID,rolegroup_365_id,STATUS,CREATEDDATE,CREATEDBY,source) VALUES('" + parent_id + "','" + qw_parentid + "',1,now(),'000023','2')";
                        eliteBean.update(insertSql5, 'dataSource');
                    }
                }


                if (selelctList.size() > 0) {
                    //存在修改
                    log.debug("Elite系统存在该组别ID执行修改操作");
                    String upsql = '''
                                  UPDATE ROLEGROUP SET 
                                     ROLEGROUPDESCRIPTION=?,
                                     PARENT_ROLEGROUPID=? ,
                                     MODIFIEDDATE=now(),
                                     MODIFIEDBY='00023',
                                     source='2'                  
                                      WHERE ROLEGROUP_ID=?
                                    ''';
                    jt.update(upsql, qw_name, parent_id, rolegroup_id);
                    //先删后增
                    def updateSql3 = "delete from authority_relationship where lower='" + rolegroup_id + "' and HIGHERFLAG='0' and LOWERFLAG='0'";
                    def row = eliteBean.update(updateSql3, 'dataSource');
                    log.debug("删除authority_relationship层级:" + rolegroup_id + "|" + row);
                    String insertSql4 = '''
                                INSERT INTO authority_relationship ( AUTHORITY_ID, HIGHER, HIGHERFLAG, LOWER, LOWERFLAG ) VALUES( uuid(), ?, 0,?, 0 ) 
                                ''';
                    jt.update(insertSql4, parent_id, rolegroup_id);

                } else {
                    //在系统表里找不到 组别--->开始新增组别
                    def ram_id = KHUtil.getRandomString(6).toUpperCase();//随机id
                    def insertSql1 = "INSERT INTO ROLEGROUP (ROLEGROUP_ID,rolegroup_365_id,ROLEGROUPDESCRIPTION,STATUS,PARENT_ROLEGROUPID,CREATEDDATE,CREATEDBY,source) VALUES('" + ram_id + "','" + qw_id + "','" + qw_name + "','1','" + parent_id + "',now(),'000023','2')";
                    def dataresult = eliteBean.update(insertSql1, 'dataSource');
                    log.debug("执行ROLEGROUP数据新增SQL结果:" + dataresult.toString());


                    //插入表mprojectrolegroup话务员组和项目对应表
                    def insertsql3 = "INSERT INTO mprojectrolegroup(ROLEGROUP_ID, MPROJECT_ID, MPROJECTROLEGROUP_ID) VALUES ('" + ram_id + "', 'MMYSQL', uuid())";
                    def dataresult3 = eliteBean.update(insertsql3, 'dataSource');
                    log.debug("执行mprojectrolegroup数据新增SQL结果:" + dataresult3.toString());


                    if (!KHUtil.isNull(parent_id)) {
                        //获取父级关系--入层级表
                        def insertSql2 = "INSERT INTO authority_relationship ( AUTHORITY_ID, HIGHER, HIGHERFLAG, LOWER, LOWERFLAG ) VALUES( uuid(), '" + parent_id + "', 0, '" + ram_id + "', 0 )";
                        def dataresult2 = eliteBean.update(insertSql2, 'dataSource');
                        log.debug("执行authority_relationship数据新增SQL结果:" + dataresult2.toString());

                    }


                }

            }

            //******************************************************************开始同步365人员信息************************************************************************************************
            String staffsql = '''
                                SELECT
                                r1.id,
                                r1.emp_id,
                                r1.emp_code,
                                r1.emp_name,
                                r1.last_use_time,
                                r1.emp_sex,
                                r1.emp_mobile,
                                r1.emp_tel,
                                r1.emp_idcard,
                                r1.emp_birthday,
                                r1.emp_email,
                                r1.emp_addr,
                                r1.emp_qq,
                                r1.emp_weixin,
                                r1.emp_org_id,
                                r1.emp_org_code,
                                r1.waiqin365_org_id,
                                r1.emp_imsi_binding,
                                r1.emp_is_org_learder,
                                r1.emp_parent_id,
                                r1.emp_position,
                                r1.emp_job,
                                r1.emp_status,
                                r1.create_time,
                                r1.modify_time,
                                r1.dealer_id,
                                r1.dealer_name,
                                r1.emp_baseprovince,
                                r1.emp_basecity,
                                r1.waiqin365_job_id,
                                r1.emp_job_code,
                                r1.waiqin365_position_id,
                                r1.emp_position_code,
                                r1.emp_enrollment_time as emp_enrollment_time ,
                                r1.emp_resignation_time  as emp_resignation_time
                                FROM
                                hds_employee_info r1 where r1.emp_position like '德高%' 
                        ''';

            //获取企微人员数据
            SqlRowSet rs2 = jt365.queryForRowSet(staffsql);

            //循环遍历
            while (rs2.next()) {
                String id = rs2.getString("id");//外勤员工唯一ID
                String emp_id = rs2.getString("emp_id");//第三方员工唯一ID
                String emp_code = rs2.getString("emp_code");//员工登录账号
                String emp_name = rs2.getString("emp_name");//姓名
                String last_use_time = rs2.getString("last_use_time");//最近登录时间
                String emp_sex = rs2.getString("emp_sex");//性别
                if (emp_sex.equals("M")) {
                    emp_sex = '1';
                } else if (emp_sex.equals("F")) {
                    emp_sex = '2';
                } else {
                    emp_sex = '0';
                }
                String emp_mobile = rs2.getString("emp_mobile");//员工手机号
                String emp_tel = rs2.getString("emp_tel");//固定电话
                String emp_idcard = rs2.getString("emp_idcard");//身份证号
                String emp_birthday = rs2.getString("emp_birthday") == '' ? null : rs2.getString("emp_birthday");//生日
                String emp_email = rs2.getString("emp_email");//邮箱
                String emp_addr = rs2.getString("emp_addr");//地址
                String emp_qq = rs2.getString("emp_qq");//QQ
                String emp_weixin = rs2.getString("emp_weixin");//微信
                String emp_org_id = rs2.getString("emp_org_id");//第三方隶属部门
                String emp_org_code = rs2.getString("emp_org_code");//隶属部门编码
                String waiqin365_org_id = rs2.getString("waiqin365_org_id");//外勤部门唯一ID
                String emp_imsi_binding = rs2.getString("emp_imsi_binding");//手机卡绑定
                String emp_is_org_learder = rs2.getString("emp_is_org_learder");//是否机构领导
                String emp_parent_id = rs2.getString("emp_parent_id");//第三方上次唯一ID
                String emp_position = rs2.getString("emp_position");//职务名称
                String emp_job = rs2.getString("emp_job");//岗位名称
                String emp_status = rs2.getString("emp_status");//员工账号状态
                String dealer_id = rs2.getString("dealer_id");//经销商唯一ID
                String dealer_name = rs2.getString("dealer_name");//经销商中文名称
                String emp_baseprovince = rs2.getString("emp_baseprovince");//常驻地省信息
                String emp_basecity = rs2.getString("emp_basecity");//常驻地市信息
                String waiqin365_job_id = rs2.getString("waiqin365_job_id");//岗位ID
                String emp_job_code = rs2.getString("emp_job_code");//岗位编码
                String waiqin365_position_id = rs2.getString("waiqin365_position_id");//职务ID
                String emp_position_code = rs2.getString("emp_position_code");//职务编码
                String emp_enrollment_time = rs2.getString("emp_enrollment_time") == '' ? null : rs2.getString("emp_enrollment_time");//入职时间
                String emp_resignation_time = rs2.getString("emp_resignation_time") == '' ? null : rs2.getString("emp_resignation_time");//离职时间
                log.debug("[准备处理同步的人员]：" + id + "---[姓名]" + emp_name);
                String workstatus = null;
                String ISSUPERVISOR = null;
                String rolegroup_id = null;
                String staff_id = null;
                def ram_id = KHUtil.getRandomString(6).toUpperCase();//随机id
                //翻译------如果激活状态是5  那么就标记成离职
                if (emp_status.equals("2") || emp_status.equals("0")) {
                    workstatus = "QUIT";
                    emp_name = emp_name + "-停用";
                } else if (emp_status.equals("1")) {
                    workstatus = "ONLINE";
                }

                //判断人员是否已经存在
                String selectSql = '''SELECT STAFF_ID FROM STAFF WHERE id_365=?  limit 1 ''';
                List<Map<String, Object>> selelctList = jt.queryForList(selectSql, id);
                if (selelctList.size() > 0) {
                    staff_id = selelctList.get(0).get("STAFF_ID");
                }


                //判断部门是否存在
                def selectSql3 = "select ROLEGROUP_ID from ROLEGROUP where rolegroup_365_id='" + waiqin365_org_id + "' limit 1";
                List sqlResult3 = eliteBean.chaxun_sql(selectSql3, 'dataSource');
                log.debug(sqlResult3.toString());
                if (sqlResult3.size() > 0) {
                    rolegroup_id = sqlResult3.get(0).get("ROLEGROUP_ID");
                } else {
                    log.debug("系统找不到该组：" + rolegroup_id);

                }


                if (selelctList.size() > 0) {
                    log.debug("已经存在staff表：" + staff_id);
                    //判断已经存在 ----执行修改
                    String updatesql = '''
                                    update staff set 
                                 staffname=?,
                                 WORKSTATUS=?,
                                 loginname=?,
                                 defaultrolegroup=?,
                                 e_mail=?,
                                 GENDER=?,
                                 MOBILE=?,
                                 TEL_1=?,
                                 emp_idcard_365=?,
                                 emp_id_365=?,
                                 BIRTHDAY=?,
                                 ADDRESS=?,
                                 QQ=?,
                                 WEIXINID=?,
                                 emp_org_id_365=?,
                                emp_org_code_365=? ,
                                emp_is_org_learder_365=?,
                                 emp_imsi_binding_365=?,  
                                 emp_parent_id_365=?,   
                                 emp_position_365=?,  
                                 emp_job_365=?,       
                                 dealer_id_365=?, 
                                 dealer_name_365=?,     
                                 emp_baseprovince_365=?,
                                 
                                  emp_basecity_365=?,
                                  waiqin365_job_id_365=?,
                                  emp_job_code_365=?,
                                  waiqin365_position_id_365=?,
                                  emp_position_code_365=?,
                                  EMPLOYEDDATE=?,
                                  LEAVE_TIME=?,
                                  PASSWORD=?,
                                  id_365=?,
                                 sync_time_365=now(),
                                 MODIFIEDBY='000023',
                                 MODIFIEDDATE=now(),
                                 source='2'
                                 where staff_id=?
                                 ''';


                    //增加补丁
                    String updatecustsql =''' update customer set MOBILE=?,customername=? where CUSTOMER_ID=? and customer_source='360' '''
                    jt.update(updatecustsql,emp_mobile,emp_name,id);
                    jt.update(updatesql, emp_name, workstatus, emp_code, rolegroup_id, emp_email, emp_sex, emp_mobile, emp_tel,
                            emp_idcard, emp_id, emp_birthday, emp_addr, emp_qq, emp_weixin, emp_org_id,
                            emp_org_code, emp_is_org_learder, emp_imsi_binding, emp_parent_id, emp_position, emp_job, dealer_id, dealer_name, emp_baseprovince,
                            emp_basecity, waiqin365_job_id, emp_job_code, waiqin365_position_id, emp_position_code, emp_enrollment_time, emp_resignation_time, emp_code, id,
                            staff_id);

                    log.debug("查询到已有" + staff_id + "，执行更新staffrolegroup数据....");
                    String updateSql3 = "delete from staffrolegroup where staff_id='" + staff_id + "'";
                    int dataresult4 = eliteBean.update(updateSql3, 'dataSource');
                    log.debug("删除staffrolegroup:" + staff_id + "|" + dataresult4);

                    String updateSql2 = "INSERT INTO staffrolegroup(STAFF_ID, ROLEGROUP_ID) VALUES ('" + staff_id + "', '" + rolegroup_id + "')";
                    int dataresult5 = eliteBean.update(updateSql2, 'dataSource');
                    log.debug("更新staffrolegroup数据结果:" + staff_id + "|" + dataresult5);

                } else {
                    //查询不到执行新增staff

                    String insertsql = '''
                                 insert 
                                 into 
                                 staff 
                                 (
                                 staff_id,
                                 staffname,
                                 WORKSTATUS,
                                 ISSUPERVISOR,
                                 loginname,
                                 defaultrolegroup,
                                 e_mail,
                                 GENDER,
                                 MOBILE,
                                 TEL_1,
                                 emp_idcard_365,
                                 emp_id_365,
                                 BIRTHDAY,
                                 ADDRESS,
                                 QQ,
                                 WEIXINID,
                                 emp_org_id_365,
                                emp_org_code_365 ,
                                emp_is_org_learder_365,
                                 emp_imsi_binding_365,  
                                 emp_parent_id_365,   
                                 emp_position_365,  
                                 emp_job_365,       
                                 dealer_id_365, 
                                 dealer_name_365,     
                                 emp_baseprovince_365,
                                  emp_basecity_365,
                                  waiqin365_job_id_365,
                                  emp_job_code_365,
                                  waiqin365_position_id_365,
                                  emp_position_code_365,
                                  EMPLOYEDDATE,
                                  LEAVE_TIME,
                                PASSWORD,
                                id_365,
                                 sync_time_365,
                                 CREATEDBY,
                                 CREATEDDATE,
                                  source
                                 )
                                 values
                                 (
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?, 
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 ?,
                                 now(),
                                '000023',
                                 now(),
                                  '2'
                                 )
                                  ''';
                    jt.update(insertsql, ram_id,
                            emp_name,
                            workstatus, 1,
                            emp_code,
                            rolegroup_id,
                            emp_email,
                            emp_sex,
                            emp_mobile,
                            emp_tel,
                            emp_idcard,
                            emp_id, emp_birthday, emp_addr, emp_qq, emp_weixin, emp_org_id,
                            emp_org_code, emp_is_org_learder, emp_imsi_binding, emp_parent_id, emp_position, emp_job, dealer_id, dealer_name, emp_baseprovince,
                            emp_basecity, waiqin365_job_id, emp_job_code, waiqin365_position_id, emp_position_code, emp_enrollment_time, emp_resignation_time, emp_code, id);

                    String insertSql2 = "INSERT INTO staffrolegroup(STAFF_ID, ROLEGROUP_ID) VALUES ('" + ram_id + "', '" + rolegroup_id + "')";
                    int dataresult2 = eliteBean.update(insertSql2, 'dataSource');
                    log.debug("执行staffrolegroup数据新增SQL结果:" + ram_id + "|" + dataresult2);

                    //增加补丁
                    String insertcustsql =''' insert into customer (CUSTOMER_GUID,CUSTOMERNAME,MOBILE,CUSTOMER_ID,customer_source) values(uuid(),?,?,?,?)'''
                    jt.update(insertcustsql,emp_name,emp_mobile,id,'365');

                }
            }

            long endtime = System.currentTimeMillis();

            log.debug("【365同步人员组别信息】执行耗时:" + (endtime - starttime));

        } catch (Exception e) {
            log.error("【365数据库同步人员组别信息异常】：" + e);

        }
    }

}
